from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
from datetime import datetime

def create_material_template(output_path, new_data=None):
    """
    创建物资领料单模板
    :param output_path: 输出文件路径
    :param new_data: 新数据字典（可选），格式示例：
        {
            "使用单位": "办公楼保洁组",
            "领料单号": "BJ202504015003",
            "items": [
                {"物资名称": "擦手纸", "规格型号": "20包/箱", "单位": "包", "数量": 10, "单价": 8.18, "备注": "办公楼A1"},
                # 更多物品...
            ]
        }
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "物资领料单"
    
    # === 设置样式 ===
    title_font = Font(name='宋体', size=14, bold=True)
    header_font = Font(name='宋体', size=11, bold=True)
    normal_font = Font(name='宋体', size=11)
    border = Border(left=Side(style='thin'), 
                   right=Side(style='thin'), 
                   top=Side(style='thin'), 
                   bottom=Side(style='thin'))
    center_alignment = Alignment(horizontal='center', vertical='center')
    
    # === 写入标题行 ===
    ws.merge_cells('A1:I1')
    title_cell = ws['A1']
    title_cell.value = "物   资  领  料  单"
    title_cell.font = title_font
    title_cell.alignment = center_alignment
    
    # === 写入单位信息行 ===
    ws.merge_cells('A2:I2')
    unit_info = f"使用单位：{new_data['使用单位'] if new_data else '使用单位：'}                                     ：{new_data['领料单号'] if new_data else '领料单号：'}"
    ws['A2'].value = unit_info
    ws['A2'].font = normal_font
    
    # === 写入表头 ===
    headers = ["序号", "物资名称", "规格型号", "单位", "数量", "单价", "金额", "备注（用途）", "领用人"]
    for col, header in enumerate(headers, start=1):
        cell = ws.cell(row=3, column=col, value=header)
        cell.font = header_font
        cell.border = border
        cell.alignment = center_alignment
    
    # === 写入数据行 ===
    if new_data and 'items' in new_data:
        for i, item in enumerate(new_data['items'], start=4):
            # 序号
            ws.cell(row=i, column=1, value=i-3).alignment = center_alignment
            # 物资名称
            ws.cell(row=i, column=2, value=item.get("物资名称", ""))
            # 规格型号
            ws.cell(row=i, column=3, value=item.get("规格型号", ""))
            # 单位
            ws.cell(row=i, column=4, value=item.get("单位", ""))
            # 数量
            ws.cell(row=i, column=5, value=item.get("数量", ""))
            # 单价
            ws.cell(row=i, column=6, value=item.get("单价", ""))
            # 金额（公式）
            ws.cell(row=i, column=7, value=f"=E{i}*F{i}")
            # 备注
            ws.cell(row=i, column=8, value=item.get("备注", ""))
            # 领用人
            ws.cell(row=i, column=9, value=item.get("领用人", ""))
            
            # 应用样式
            for col in range(1, 10):
                ws.cell(row=i, column=col).font = normal_font
                ws.cell(row=i, column=col).border = border
    
    # === 写入小计行 ===
    if new_data and 'items' in new_data:
        last_row = 3 + len(new_data['items'])
        ws.cell(row=last_row+1, column=1, value="").border = border
        ws.merge_cells(f'B{last_row+1}:D{last_row+1}')
        ws.cell(row=last_row+1, column=2, value="小计（金额）").font = header_font
        ws.cell(row=last_row+1, column=5, value=f"=SUM(G4:G{last_row})").font = header_font
        for col in [2,5,6,7,8,9]:
            ws.cell(row=last_row+1, column=col).border = border
    
    # === 设置列宽 ===
    column_widths = [8, 15, 15, 8, 8, 8, 8, 15, 10]
    for i, width in enumerate(column_widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = width
    
    # === 保存文件 ===
    wb.save(output_path)

# 使用示例
if __name__ == "__main__":
    # 示例数据
    sample_data = {
        "使用单位": "办公楼保洁组",
        "领料单号": "BJ202504015003",
        "items": [
            {"物资名称": "擦手纸", "规格型号": "20包/箱", "单位": "包", "数量": 10, "单价": 8.18, "备注": "办公楼A1"},
            {"物资名称": "大盘纸", "规格型号": "12卷/箱", "单位": "卷", "数量": 8, "单价": 12.17, "备注": ""},
            {"物资名称": "全能清", "规格型号": "3.8L", "单位": "桶", "数量": 1, "单价": 21.69, "备注": ""},
            {"物资名称": "洗手液", "规格型号": "5L/桶", "单位": "桶", "数量": 1, "单价": 81.92, "备注": ""}
        ]
        
    }
    
    # 生成模板
    create_material_template("物资领料单模板.xlsx", sample_data)
    print("模板生成完成！")